/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package MODEL.DAO;
import MODEL.POJO.*;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;


public class TestResultsDao {
    private static int last_insert_id;
     public static List<Testresults> LayDanhSachKetQua(){
        List<Testresults> ds = null; 
        //Session session = HibernateUtil.getSessionFactory().openSession();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try { 
            String hql = "select kq from Testresults kq"; 
            Query query = session.createQuery(hql); 
            ds = query.list(); 
        } catch (HibernateException ex) { 
            System.err.println(ex); 
        } finally { 
           //session.close(); 
        } 
        return ds; 
    }
    // Lấy kết quả theo mã
     public static Testresults GetResultByCode(int id) {
        Testresults kq = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            kq = (Testresults) session.get(Testresults.class, id);
        } catch (HibernateException ex) {
            //Log the exception
            System.err.println(ex);
        } finally {
            //session.close();
        }
        return kq;
    }
     
      public static List<Testresults> LayDanhSachKetQuaID(int ma){
        List<Testresults> ds = null; 
        //Session session = HibernateUtil.getSessionFactory().openSession();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try { 
            String hql = "from Testresults kq where kq.users.userId = :ma ";
            Query query = session.createQuery(hql); 
            query.setParameter("ma", ma);
            ds = query.list(); 
        } catch (HibernateException ex) { 
            System.err.println(ex); 
        } finally { 
           //session.close(); 
        } 
        return ds; 
    }
      
    public static boolean addResult(Testresults result) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        boolean kq = true;
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
            session.save(result);
            session.flush();
            last_insert_id = result.getTestResultId();
            transaction.commit();
        } catch (HibernateException ex) {
            //Log the exception
            transaction.rollback();
            System.err.println(ex.getMessages());
            kq = false;
        } finally {
            //session.close();
        }
        return kq;
    }
    public static int getMaxIDofUser(int level, int Identify){
        int max = 0;
        Session session = HibernateUtil.getSessionFactory().openSession(); 
        try { 
            String hql = "Select MAX(tr.warehouses.warehouseNumber) from Testresults tr, Users u, Warehouses wh, Customizequizs cq where tr.warehouses.warehouseId = wh.warehouseId and tr.users.userId = u.userId and u.userIdentityCard=:Identify and wh.customizequizs.customizeQuizId =:level and wh.customizequizs.customizeQuizId = cq.customizeQuizId"; 
            Query query = session.createQuery(hql);
            query.setParameter("Identify", Identify);
            query.setParameter("level", level);
            if(query.uniqueResult() != null)
                max = Integer.parseInt(query.uniqueResult().toString());
        } catch (HibernateException ex) { 
            System.err.println(ex); 
        } finally { 
            session.close(); 
        } 
        return max;
    }
    public static Testresults getResultFromExamCode(int examCode, int idUser){
        Testresults result = null;
        Session session = HibernateUtil.getSessionFactory().openSession(); 
        try { 
            String hql = "from Testresults tr where tr.warehouses.warehouseId =:examCode and tr.users.userId =:idUser"; 
            Query query = session.createQuery(hql);
            query.setParameter("examCode", examCode);
            query.setParameter("idUser", idUser);
            result = (Testresults)query.list().get(0); 
        } catch (HibernateException ex) { 
            System.err.println(ex); 
        } finally { 
            //session.close(); 
        } 
        return result;
    }
    
     public static boolean updateResult(Testresults tr) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
            session.update(tr);
            transaction.commit();
        } catch (HibernateException ex) {
            //Log the exception
            transaction.rollback();
            System.err.println(ex);
        } finally {
            session.close();
        }
        return true;
    }

    public static int DemSoLuongBaiThiTheoDiem(float diem, int iduser)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        int count = 0 ;
        List<Testresults> ds =null;
          try {
             // from Testresults t where t.testResultScore=4 and t.users =1
              //from Testresults t join t.users u where t.testResultScore = 4 and u.userId = 1
              //from Testresults t where t.testResultScore = 4 and t.users.userId = 1
        String HQL = "from Testresults t where t.testResultScore = :diem and t.users.userId = :iduser";             
        Query query = session.createQuery(HQL); 
        query.setParameter("diem", diem);
        query.setParameter("iduser", iduser);
        ds = query.list();
        count = ds.size();
        } catch (HibernateException ex) { 
            System.err.println(ex); 
        } finally { 
           session.close(); 
        } 
        
        return count;
    }

    /**
     * @return the last_insert_id
     */
    public static int getLast_insert_id() {
        return last_insert_id;
    }

    /**
     * @param aLast_insert_id the last_insert_id to set
     */
    public static void setLast_insert_id(int aLast_insert_id) {
        last_insert_id = aLast_insert_id;
    }
}
